/*
 *    GeoTools - The Open Source Java GIS Toolkit
 *    http://geotools.org
 *
 *    (C) 2002-2011, Open Source Geospatial Foundation (OSGeo)
 *
 *    This library is free software; you can redistribute it and/or
 *    modify it under the terms of the GNU Lesser General Public
 *    License as published by the Free Software Foundation;
 *    version 2.1 of the License.
 *
 *    This library is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *    Lesser General Public License for more details.
 */
package org.geotools.data.teradata;

import org.geotools.jdbc.JDBCPrimaryKeyTestSetup;
import org.geotools.jdbc.JDBCTestSetup;

public class TeradataPrimaryKeyTestSetup extends JDBCPrimaryKeyTestSetup {

    public TeradataPrimaryKeyTestSetup(JDBCTestSetup delegate) {
        super(delegate);
    }

    public TeradataTestSetup getDelegate() {
        return (TeradataTestSetup) delegate;
    }

    @Override
    protected void createAutoGeneratedPrimaryKeyTable() throws Exception {
        run(
                "CREATE TABLE \"auto\"(\"key\" PRIMARY KEY not null generated always as identity (start with 1) integer, \"name\" varchar(200), geom ST_Geometry)");
        run(
                "INSERT INTO SYSSPATIAL.GEOMETRY_COLUMNS (F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME, "
                        + "F_GEOMETRY_COLUMN, COORD_DIMENSION, SRID, GEOM_TYPE) VALUES ('','test','auto', 'geom', "
                        + "2, "
                        + getDelegate().getSrid4326()
                        + ", 'ST_Geometry')");
        run("INSERT INTO \"auto\" (\"name\",\"geom\" ) VALUES ('one',NULL)");
        run("INSERT INTO \"auto\" (\"name\",\"geom\" ) VALUES ('two',NULL)");
        run("INSERT INTO \"auto\" (\"name\",\"geom\" ) VALUES ('three',NULL)");
    }

    @Override
    protected void createMultiColumnPrimaryKeyTable() throws Exception {
        run(
                "CREATE TABLE \"multi\" ( \"key1\" int NOT NULL, \"key2\" varchar(40) NOT NULL, "
                        + "\"name\" varchar(40), geom ST_Geometry)");
        run(
                "INSERT INTO SYSSPATIAL.GEOMETRY_COLUMNS (F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME, "
                        + "F_GEOMETRY_COLUMN, COORD_DIMENSION, SRID, GEOM_TYPE) VALUES ('','test','multi', 'geom', "
                        + "2, "
                        + getDelegate().getSrid4326()
                        + ", 'ST_Geometry')");
        run("ALTER TABLE \"multi\" ADD PRIMARY KEY (\"key1\",\"key2\")");

        run("INSERT INTO \"multi\" VALUES (1, 'x', 'one', NULL)");
        run("INSERT INTO \"multi\" VALUES (2, 'y', 'two', NULL)");
        run("INSERT INTO \"multi\" VALUES (3, 'z', 'three', NULL)");
    }

    @Override
    protected void createNonIncrementingPrimaryKeyTable() throws Exception {
        run(
                "CREATE TABLE \"noninc\"(\"key\" PRIMARY KEY NOT NULL integer, \"name\" varchar(200), geom ST_Geometry)");
        run(
                "INSERT INTO SYSSPATIAL.GEOMETRY_COLUMNS (F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME, "
                        + "F_GEOMETRY_COLUMN, COORD_DIMENSION, SRID, GEOM_TYPE) VALUES ('','test','noninc', 'geom',"
                        + "2, "
                        + getDelegate().getSrid4326()
                        + ", 'ST_Geometry')");

        run("INSERT INTO \"noninc\" VALUES (1, 'one', NULL)");
        run("INSERT INTO \"noninc\" VALUES (2, 'two', NULL)");
        run("INSERT INTO \"noninc\" VALUES (3, 'three', NULL)");
    }

    @Override
    protected void createSequencedPrimaryKeyTable() throws Exception {
        run(
                "CREATE TABLE \"seq\" ( \"key\" generated always as identity (start with 1)  integer, \"name\" varchar(200), geom ST_Geometry)");
        run(
                "INSERT INTO SYSSPATIAL.GEOMETRY_COLUMNS (F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME, "
                        + "F_GEOMETRY_COLUMN, COORD_DIMENSION, SRID, GEOM_TYPE) VALUES ('','test','seq', 'geom', 2, "
                        + getDelegate().getSrid4326()
                        + ", 'ST_Geometry')");
        //        run("CREATE SEQUENCE SEQ_KEY_SEQUENCE START WITH 1 OWNED BY \"seq\".\"key\"");

        /*        run("INSERT INTO \"seq\" (\"key\", \"name\",\"geom\" ) VALUES (" +
        "(SELECT NEXTVAL('SEQ_KEY_SEQUENCE')),'one',NULL)");
        run("INSERT INTO \"seq\" (\"key\", \"name\",\"geom\" ) VALUES (" +
        "(SELECT NEXTVAL('SEQ_KEY_SEQUENCE')),'two',NULL)");
        run("INSERT INTO \"seq\" (\"key\", \"name\",\"geom\" ) VALUES (" +
        "(SELECT NEXTVAL('SEQ_KEY_SEQUENCE')),'three',NULL)");*/

        run("INSERT INTO \"seq\" (\"name\",\"geom\") VALUES ('one',NULL)");
        run("INSERT INTO \"seq\" (\"name\",\"geom\") VALUES ('two',NULL)");
        run("INSERT INTO \"seq\" (\"name\",\"geom\") VALUES ('three',NULL)");
    }

    @Override
    protected void createNullPrimaryKeyTable() throws Exception {
        run("CREATE TABLE \"nokey\" ( \"name\" varchar(200))");
        run("INSERT INTO \"nokey\" VALUES ('one')");
        run("INSERT INTO \"nokey\" VALUES ('two')");
        run("INSERT INTO \"nokey\" VALUES ('three')");
    }

    @Override
    protected void createUniqueIndexTable() throws Exception {
        run(
                "CREATE TABLE \"uniq\"(\"key\" UNIQUE NOT NULL int, \"name\" varchar(200), geom ST_Geometry)");
        run(
                "INSERT INTO SYSSPATIAL.GEOMETRY_COLUMNS (F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME, "
                        + "F_GEOMETRY_COLUMN, COORD_DIMENSION, SRID, GEOM_TYPE) VALUES ('','test','uniq', 'geom', 2, "
                        + getDelegate().getSrid4326()
                        + ", 'ST_Geometry')");

        //        run("CREATE UNIQUE INDEX \"uniq_key_index\" ON \"uniq\"(\"key\")");
        run("INSERT INTO \"uniq\" VALUES (1,'one',NULL)");
        run("INSERT INTO \"uniq\" VALUES (2,'two',NULL)");
        run("INSERT INTO \"uniq\" VALUES (3,'three',NULL)");
        createExtraTables();
    }

    @Override
    protected void createNonFirstColumnPrimaryKey() throws Exception {
        run(
                "CREATE TABLE \"nonfirst\"(\"name\" varchar(200), \"key\" PRIMARY KEY not null generated always as identity (start with 1) integer, geom ST_Geometry)");
        run(
                "INSERT INTO SYSSPATIAL.GEOMETRY_COLUMNS (F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME, "
                        + "F_GEOMETRY_COLUMN, COORD_DIMENSION, SRID, GEOM_TYPE) VALUES ('','test','auto', 'geom', "
                        + "2, "
                        + getDelegate().getSrid4326()
                        + ", 'ST_Geometry')");
        run("INSERT INTO \"nonfirst\" (\"name\",\"geom\" ) VALUES ('one',NULL)");
        run("INSERT INTO \"nonfirst\" (\"name\",\"geom\" ) VALUES ('two',NULL)");
        run("INSERT INTO \"nonfirst\" (\"name\",\"geom\" ) VALUES ('three',NULL)");
    }

    @Override
    protected void dropAutoGeneratedPrimaryKeyTable() throws Exception {
        runSafe("DELETE FROM SYSSPATIAL.GEOMETRY_COLUMNS WHERE F_TABLE_NAME = 'auto'");
        runSafe("DROP TABLE \"auto\"");
    }

    @Override
    protected void dropMultiColumnPrimaryKeyTable() throws Exception {
        runSafe("DELETE FROM SYSSPATIAL.GEOMETRY_COLUMNS WHERE F_TABLE_NAME = 'multi'");
        runSafe("DROP TABLE \"multi\"");
    }

    @Override
    protected void dropNonIncrementingPrimaryKeyTable() throws Exception {
        runSafe("DELETE FROM SYSSPATIAL.GEOMETRY_COLUMNS WHERE F_TABLE_NAME = 'noninc'");
        runSafe("DROP TABLE \"noninc\"");
    }

    @Override
    protected void dropSequencedPrimaryKeyTable() throws Exception {
        runSafe("DELETE FROM SYSSPATIAL.GEOMETRY_COLUMNS WHERE F_TABLE_NAME = 'seq'");
        runSafe("DROP SEQUENCE SEQ_KEY_SEQUENCE");
        runSafe("DROP TABLE \"seq\"");
    }

    @Override
    protected void dropNullPrimaryKeyTable() throws Exception {
        runSafe("DROP TABLE \"nokey\"");
    }

    @Override
    protected void dropUniqueIndexTable() throws Exception {
        runSafe("DELETE FROM SYSSPATIAL.GEOMETRY_COLUMNS WHERE F_TABLE_NAME = 'uniq'");
        runSafe("DROP TABLE \"uniq\"");
        dropExtraTables();
    }

    @Override
    protected void dropNonFirstPrimaryKeyTable() throws Exception {
        runSafe("DELETE FROM SYSSPATIAL.GEOMETRY_COLUMNS WHERE F_TABLE_NAME = 'nonfirst'");
        runSafe("DROP TABLE \"nonfirst\"");
    }

    private void createExtraTables() throws Exception {
        run(
                "CREATE TABLE \"uniquetablenotgenerated\" ( \"key\" UNIQUE not null integer, \"name\" VARCHAR(256), \"geom\" ST_GEOMETRY)");
        run(
                "INSERT INTO SYSSPATIAL.GEOMETRY_COLUMNS (F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME, "
                        + "F_GEOMETRY_COLUMN, COORD_DIMENSION, SRID, GEOM_TYPE) VALUES ('','"
                        + fixture.getProperty("schema")
                        + "','uniquetablenotgenerated', 'geom', 2, "
                        + getDelegate().getSrid4326()
                        + ", 'GEOMETRY')");
        insertFeatures("uniquetablenotgenerated");

        run(
                "CREATE TABLE \"uniquetable\" ( \"key\" UNIQUE generated always as identity (start with 1) integer not null, \"name\" VARCHAR(256), \"geom\" ST_GEOMETRY)");
        run(
                "INSERT INTO SYSSPATIAL.GEOMETRY_COLUMNS (F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME, "
                        + "F_GEOMETRY_COLUMN, COORD_DIMENSION, SRID, GEOM_TYPE) VALUES ('','"
                        + fixture.getProperty("schema")
                        + "','uniquetable', 'geom', 2, "
                        + getDelegate().getSrid4326()
                        + ", 'GEOMETRY')");
        insertFeatures("uniquetable");
    }

    private void dropExtraTables() throws Exception {
        runSafe("DROP TABLE \"uniquetablenotgenerated\"");
        runSafe(
                "delete from SYSSPATIAL.GEOMETRY_COLUMNS where f_table_name='uniquetablenotgenerated'");
        runSafe("DROP TABLE \"uniquetable\"");
        runSafe("delete from SYSSPATIAL.GEOMETRY_COLUMNS where f_table_name='uniquetable'");
    }

    private void insertFeatures(String tableName) throws Exception {
        run(
                "INSERT INTO \""
                        + tableName
                        + "\" (\"key\", \"name\",\"geom\" ) VALUES (1,'one',NULL)");
        run(
                "INSERT INTO \""
                        + tableName
                        + "\" (\"key\", \"name\",\"geom\" ) VALUES (2,'two',NULL)");
        run(
                "INSERT INTO \""
                        + tableName
                        + "\" (\"key\", \"name\",\"geom\" ) VALUES (3,'three',NULL)");
    }
}
